package cj.web.sc.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface SCCrowdFundOrderDAO {
	
	@SQL("SELECT a.sellerId,s.name FROM SCCart a LEFT JOIN SCSeller s ON a.sellerId =s.id WHERE a.deleteFlag=1 AND a.buyerId=:1 GROUP BY a.sellerId,s.name  ")
	List<Map> cartSellerByBuyerId(int buyerId);

	@SQL("select a.id,a.goodsItemId,a.count,a.status,i.name,i.propValueNames,i.discntPrice,i.image,i.goodsId,a.sellerId from SCCart a LEFT JOIN SCGoodsItems i on a.goodsItemId=i.id where a.deleteFlag=1 and a.buyerId=:1 ORDER BY a.sellerId ")
	List<Map> cartByBuyerId(int buyerId);
	
	@SQL("UPDATE SCCart SET count=:2 WHERE id=:1 ")
	void updateCartById(int cartId,int count);
	
	@SQL("delete from SCCart where id in (:1)")
	void delCartByIds(List<Integer> arrIds);

	@ReturnGeneratedKeys
	@SQL("INSERT INTO SCCrowdFundOrder( buyerId,sellerId,sellerName,sellerLogo,orderNo,discntPrice,receiverName,receiverPhone,receiverAddress,isInvoice,invoiceHead,memo,status,deleteFlag,createAt,createUser,updateAt,UpdateUser,gameId,code,progress) select :1,:2,s.name,s.logo,:3,:4,:5,:6,:7,:8,:9,:10,:11,1,NOW(),:12,NOW(),:12,:13,:14,:15 from SCSeller s where s.id=:2 ")
	int createOrder(int buyerId, int sellerId, String orderNo,
			String discntPrice, String receiverName, String receiverPhone,
			String receiverAddress, int isInvoice, 
			String invoiceHead, String memo, int status, String phone,int gameId,String code,String progress);

	@SQL("INSERT INTO SCCrowdFundOrderItems(orderId,goodsItemId,goodsItemName,goodsItemImage,goodsItemPrice,propValueNames,count,deleteFlag,createUser,createAt,UpdateUser,UpdateAt) select :1,:2,i.name,i.image,i.discntPrice,i.propValueNames,:3,1,:4,NOW(),:4,NOW() from SCGoodsItems i WHERE i.id=:2")
	void createOrderItem(int orderId, int goodsItemId, int count, String phone);

	@SQL("select * from (select id,orderNo,discntPrice,status,sellerName,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and status>0 and buyerId=:1 order by status,createAt DESC) as table1 UNION select * from (select id,orderNo,discntPrice,status,sellerName,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and status=0 and buyerId=:1 order by createAt DESC) as table2 LIMIT :2,:3 ")
	List<Map> getOrders(int buyerId,int start, int length);
	
	@SQL("select count(1) from SCCrowdFundOrder where deleteFlag=1 and buyerId=:1 ")
	int getOrderSize(int buyerId);
	
	@SQL("select * from (select id,orderNo,discntPrice,status,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and status>0 and buyerId=:1 order by status,createAt DESC) as table1 UNION select * from (select id,orderNo,discntPrice,status,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and status=0 and buyerId=:1 order by createAt DESC) as table2 ")
	List<Map> getOrders(int buyerId);

	@SQL("select goodsItemId,goodsItemName,goodsItemImage,goodsItemPrice,propValueNames,count from SCCrowdFundOrderItems where deleteFlag=1 and orderId=:1")
	List<Map> getItemsByOrderId(int orderId);

	@SQL("select id,sellerName,orderNo,discntPrice,receiverName,receiverPhone,receiverAddress,status,isInvoice,invoiceHead,memo,expressCom,expressBill,createAt,updateAt,payAt,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and id=:1")
	List<Map> getOrderById(int orderId);

	@SQL("update SCCrowdFundOrder set status=0,updateAt=NOW() where id=:1")
	void cancelOrderById(int id);

	@SQL("update SCCrowdFundOrder set status=:1,payAt=NOW(),updateAt=NOW(),wxTradeNo=:3,payWay=:4 where orderNo=:2")
	void updateStatusByNO(int status, String out_trade_no, String transaction_id, int payWay);

	@SQL("select count(1) from SCCart where deleteFlag=1 and buyerId=:1")
	int getCartCntByBuyerId(int buyerId);

	@SQL("select id from SCCrowdFundOrder where orderNo=:1")
	int getIdByOrderNo(String out_trade_no);

	@SQL("update SCGoodsItems set inventory=inventory-:2,selledCnt=selledCnt+:2 where id=:1")
	void updateGoodsItemCnt(int id, int count);
	
	@SQL("select id,sellerName,orderNo,wxTradeNo,discntPrice,receiverName,receiverPhone,receiverAddress,status,isInvoice,invoiceHead,memo,expressCom,expressBill,createAt,updateAt,gameId,code,progress from SCCrowdFundOrder where deleteFlag=1 and orderNo=:1")
	List<Map> getOrderByNo(String outTradeNo);
}